﻿CREATE PROCEDURE [util].[GetJobXml]
@JobName NVARCHAR (MAX)=NULL
AS
BEGIN
--TODO: add new properties
	DECLARE @ProductVersion int
	SET @ProductVersion = CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS varchar),1) AS int)
	
	DECLARE @Statement nvarchar(max)
	SET @Statement = N'
	SELECT
		1 AS [Tag]
		,NULL AS [Parent]
		,NULL AS [Root!1!]
		,NULL AS [Job!2!Name]
		,NULL AS [Job!2!Enabled]
		,NULL AS [Job!2!Description]
		,NULL AS [Job!2!StartStepID]
		,NULL AS [Job!2!NotifyLevelEventlog]
		,NULL AS [Job!2!NotifyLevelEmail]
		,NULL AS [Job!2!NotifyLevelNetsend]
		,NULL AS [Job!2!NotifyLevelPage]
		,NULL AS [Job!2!NotifyEmailOperatorName]
		,NULL AS [Job!2!NotifyNetsendOperatorName]
		,NULL AS [Job!2!NotifyPageOperatorName]
		,NULL AS [Job!2!CategoryName]
		,NULL AS [Job!2!DeleteLevel]
		,NULL AS [Step!3!Id]
		,NULL AS [Step!3!Name!xml]
		,NULL AS [Step!3!Subsystem]
		,NULL AS [Step!3!Command!xml]
		,NULL AS [Step!3!Flags]
		,NULL AS [Step!3!OnSuccessAction]
		,NULL AS [Step!3!OnSuccessStepId]
		,NULL AS [Step!3!OnFailAction]
		,NULL AS [Step!3!OnFailStepId]
		,NULL AS [Step!3!DatabaseName]
		,NULL AS [Step!3!RetryAttempts]
		,NULL AS [Step!3!RetryInterval]
		,NULL AS [Step!3!Server]
		,NULL AS [Step!3!OutputFileName!xml]
		,NULL AS [Schedule!4!Name]
		,NULL AS [Schedule!4!Id]
		,NULL AS [Schedule!4!ActiveEndTime]
		,NULL AS [Schedule!4!ActiveStartTime]
		,NULL AS [Schedule!4!FreqInterval]
		,NULL AS [Schedule!4!FreqRecurrenceFactor]
		,NULL AS [Schedule!4!FreqRelativeInterval]
		,NULL AS [Schedule!4!FreqSubdayInterval]
		,NULL AS [Schedule!4!FreqSubdayType]
		,NULL AS [Schedule!4!FreqType]
	UNION ALL
	SELECT
		2 ,1 ,NULL
		,job.[name] AS [Job!1!Name]
		,job.[enabled] AS [Job!1!Enabled]
		,job.[description] AS [Job!1!Description]
		,job.[start_step_id] AS [Job!1!StartStepID]
		,job.[notify_level_eventlog] AS [Job!1!NotifyLevelEventlog]
		,job.[notify_level_email] AS [Job!1!NotifyLevelEmail]
		,job.[notify_level_netsend] AS [Job!1!NotifyLevelNetsend]
		,job.[notify_level_page] AS [Job!1!NotifyLevelPage]
		,ISNULL(eop.[name],'''') AS [Job!1!NotifyEmailOperatorName]
		,ISNULL(nop.[name],'''') AS [Job!1!NotifyNetsendOperatorName]
		,ISNULL(pop.[name],'''') AS [Job!1!NotifyPageOperatorName]
		,ISNULL(cat.[name],'''') AS [Job!1!CategoryName]
		,job.[delete_level] AS [Job!1!DeleteLevel]
		,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL, NULL
		,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
	FROM msdb.dbo.sysjobs AS job
	LEFT JOIN msdb.dbo.syscategories AS cat
		ON (job.[category_id] = cat.[category_id])
	LEFT JOIN msdb.dbo.sysoperators AS eop
		ON (eop.[id] = job.[notify_email_operator_id])
	LEFT JOIN msdb.dbo.sysoperators AS nop
		ON (nop.[id] = job.[notify_netsend_operator_id])
	LEFT JOIN msdb.dbo.sysoperators AS pop
		ON (pop.[id] = job.[notify_page_operator_id])
	' + CASE WHEN @JobName IS NOT NULL THEN N'WHERE job.[Name] = ''' + @JobName + N'''
	' ELSE N'' END + N'UNION ALL
	SELECT
		3, 2 ,NULL 
		,job.[name] ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL, NULL
		,stp.[step_id]
		,''<![CDATA['' + stp.[step_name] + '']]>''
		,stp.[subsystem]
		,''<![CDATA['' + stp.[command] + '']]>''
		,stp.[flags]
		,stp.[on_success_action]
		,stp.[on_success_step_id]
		,stp.[on_fail_action]
		,stp.[on_fail_step_id]
		,stp.[database_name]
		,stp.[retry_attempts]
		,stp.[retry_interval]
		,stp.[server]
		,''<![CDATA['' + stp.[output_file_name] + '']]>''
		,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL
	FROM msdb.dbo.sysjobs AS job
	INNER JOIN msdb.dbo.sysjobsteps AS stp
		ON (stp.[job_id] = job.[job_id])
	' + CASE WHEN @JobName IS NOT NULL THEN N'WHERE job.[Name] = ''' + @JobName + N'''
	' ELSE N'' END + N'UNION ALL
	SELECT
		4 ,2 ,NULL
		,job.[name] ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL, NULL
		,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL ,NULL, NULL
		,sch.[name]
		,sch.[schedule_id]
		,sch.[active_end_time]
		,sch.[active_start_time]
		,sch.[freq_interval]
		,sch.[freq_recurrence_factor]
		,sch.[freq_relative_interval]
		,sch.[freq_subday_interval]
		,sch.[freq_subday_type]
		,sch.[freq_type]
	FROM msdb.dbo.sysjobs AS job
	INNER JOIN msdb.dbo.sysjobschedules AS jsch
		ON (jsch.[job_id] = job.[job_id])
	INNER JOIN msdb.dbo.sysschedules AS sch
		ON (sch.[schedule_id] = jsch.[schedule_id])
	' +
	CASE WHEN @JobName IS NOT NULL THEN N'
	WHERE job.[Name] = ''' + @JobName + N'''
	' ELSE N'' END + N'ORDER BY [Job!2!Name], [Tag], [Parent]
	FOR XML EXPLICIT'

	EXEC(@Statement)
	RETURN(@@ERROR)
END